﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Xml;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using myrange = Microsoft.Office.Interop.Excel.Range;
using System.Xml.Serialization;

namespace xml
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            GetExcelData();
            //  StrToXml("12223 114 ");          
        }

        // List<System.Data.DataTable> list = new List<System.Data.DataTable>();
        List<string> list = new List<string>();
        public void GetExcelData()
        {
            OpenFileDialog ofd = new OpenFileDialog();

            ofd.Filter = "Excel Files|*.xls";

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                string filename = ofd.FileName;

                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

                Microsoft.Office.Interop.Excel.Workbook workbook;

                Microsoft.Office.Interop.Excel.Worksheet worksheet;

                object oMissing = System.Reflection.Missing.Value;//相当null

                workbook = excel.Workbooks.Open(filename, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

                for (int k = 1; k <= 1; k++)
                {
                    worksheet = (Worksheet)workbook.Worksheets[k];
                    //行数
                    int rowCount = worksheet.UsedRange.Rows.Count;
                    //列数
                    int colCount = worksheet.UsedRange.Columns.Count;
                    //MessageBox.Show(rowCount.ToString());
                    //MessageBox.Show(colCount.ToString());

                    Microsoft.Office.Interop.Excel.Range range1;

                    // System.Data.DataTable dt = new System.Data.DataTable();

                    for (int i = 0; i < colCount; i++)
                    {
                        range1 = worksheet.get_Range((object)worksheet.Cells[1, i + 1], (object)worksheet.Cells[1, i + 1]);
                        // dt.Columns.Add(range1.Value2.ToString());
                    }

                    string str = "";
                    for (int j = 5; j < 6; j++)
                    {
                        for (int i = 0; i < colCount; i++)
                        {
                            range1 = worksheet.get_Range((object)worksheet.Cells[j + 1, i + 1], (object)worksheet.Cells[j + 1, i + 1]);

                            if (!string.IsNullOrEmpty(range1.Value2.ToString()))
                            {
                                str = range1.Value2.ToString();
                                list.Add(str);

                                //MessageBox.Show(str);                              
                                //  MessageBox.Show(range1.Value2.ToString());
                            }

                        }
                       // MessageBox.Show(list.Count.ToString());
                        if (j == 5)
                        {
                            StrToXml(list);
                            //MessageBox.Show(str);
                            break;
                        }
                    }
                    excel.Quit();
                }
            }  
        }
        XmlDocument xmldoc = new XmlDocument();
        public void StrToXml(List<string> list)
        {  
            XmlDeclaration dec = xmldoc.CreateXmlDeclaration("1.0", "gb2312", null);
            xmldoc.AppendChild(dec);
       
            XmlElement root = xmldoc.CreateElement("c");
            root.SetAttribute("level", "file");
            XmlElement p1 = NodeWrite100("did");
            root.AppendChild(p1);

            XmlElement p1sub1 = NodeWrite111("unitid", "identifier", "", list[3] + list[4] + list[5]);
            p1.AppendChild(p1sub1);

            XmlElement p1sub2 = NodeWrite111("unittitle", "label", "簿冊標題", list[6]);
            p1.AppendChild(p1sub2);

            XmlElement p1sub3 = NodeWrite100("physdesc");
            p1.AppendChild(p1sub3);

            XmlElement p1sub3a1 = NodeWrite111("genreform", "type", "媒体", "00");
            p1sub3.AppendChild(p1sub3a1);

            XmlElement p1sub4 = NodeWrite111("origination", "label", "作成部局", list[7]);
            p1.AppendChild(p1sub4);

            XmlElement p1sub5 = NodeWrite100("langmaterial");
            p1.AppendChild(p1sub5);

            XmlElement p1sub5a1 = NodeWrite111("language", "langcode", "jpn", "01");
            p1sub3.AppendChild(p1sub5a1);
           
            XmlElement p1sub6 = NodeWrite111("physloc", "label", "館", "01");
            p1.AppendChild(p1sub6);
            
            XmlElement p1sub7 = NodeWrite111("physloc", "label", "階または室", "4A");
            p1.AppendChild(p1sub7);
          
            XmlElement p1sub8 = NodeWrite111("physloc", "label", "書架", "032");
            p1.AppendChild(p1sub8);
           
            XmlElement p1sub9 = NodeWrite111("physloc", "label", "棚", "00");
            p1.AppendChild(p1sub9);
           
            XmlElement p1sub10 = NodeWrite111("materialspec", "label", "データ種別", "1");
            p1.AppendChild(p1sub10);
         
            XmlElement p1sub11 = NodeWrite111("materialspec", "label", "目録種別", "2");
            p1.AppendChild(p1sub11);
                                    
            XmlElement p1sub12 = NodeWrite111("materialspec", "label", "簿冊番号1", "平１８法制");
            p1.AppendChild(p1sub12);
           
            XmlElement p1sub13 = NodeWrite111("materialspec", "label", "簿冊番号2", "00004");
            p1.AppendChild(p1sub13);
          
            XmlElement p1sub14 = NodeWrite111("materialspec", "label", "簿冊番号枝番", "100");
            p1.AppendChild(p1sub14);

            XmlElement p2 = NodeWrite100("acqinfo");
            root.AppendChild(p2);

            XmlElement p3 = NodeWrite100("scopecontent");
            root.AppendChild(p3);

            XmlElement p4 = NodeWrite110("accessrestrict", "type","公開状況");
            root.AppendChild(p4);

            XmlElement p5 = NodeWrite110("accessrestrict", "type", "審査状況");
            root.AppendChild(p5);

            XmlElement p6 = NodeWrite111("c", "level", "item","…");
            root.AppendChild(p6);


            //XmlElement rsub5 = xmldoc.CreateElement("langmaterial");
            //XmlElement rsubfive1 = xmldoc.CreateElement("language");

            //rsub4.InnerText = list[7];
            //r1.AppendChild(rsub4);

          
            //root.AppendChild(r1);

            //XmlElement r2 = xmldoc.CreateElement("acqinfo");

        

            xmldoc.AppendChild(root);

            xmldoc.Save("D:\\3.xml");
        }
        public XmlElement NodeWrite100(string name)
        {
            XmlElement node = xmldoc.CreateElement(name);
            return node;
        }
       
        public XmlElement NodeWrite110(string name,string type1,string value1)
        {
            XmlElement node = xmldoc.CreateElement(name);
            if (!string.IsNullOrEmpty(type1))
            {
                node.SetAttribute(type1, value1);
            }

            return node;
        }
        public XmlElement NodeWrite111(string name, string type1, string value1,string strText)
        {
            XmlElement node = xmldoc.CreateElement(name);
            if (!string.IsNullOrEmpty(type1))
            {
                node.SetAttribute(type1, value1);
            }
            if (!string.IsNullOrEmpty(strText))
            {
                node.InnerText = strText;
            }
            return node;
        }
        public XmlElement NodeWrite600(string name, string type1, string value1, string type2, string value2, string strText)
        {
            XmlElement node = xmldoc.CreateElement(name);
            if (!string.IsNullOrEmpty(type1))
            {
                node.SetAttribute(type1, value1);
            }
            if (!string.IsNullOrEmpty(type2))
            {
                node.SetAttribute(type2, type2);
            }
            if (!string.IsNullOrEmpty(strText))
            {
                node.InnerText = strText;
            }
            return node;
        }     
    }
}

        
